package com.bdjbd.util;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.time.LocalTime;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * @Description: Excel工具类
 * @author shujukuss
 * 
 */
public class ExcelUtils {

	public static final String OFFICE_EXCEL_2003_POSTFIX = "xls";
	public static final String OFFICE_EXCEL_2007_POSTFIX = "xlsx";
	
	public static final List<String> TRANSFORMDATE_LIST = Arrays.asList(
		"birth",
	    "enlistmentTime",
	    "caucusTime",
	    "highestGetTime",
	    "highestDegreeTime",
	    "graduateTime",
	    "appointTime",
	    "technologyLevelTime",
	    "militaryRankTime",
	    "technologyCategoryTime",
	    "lastTechnologyCategoryTime");

	/**
	 * @Description: 导出03xls
	 * @param sheetTitle sheet标题
	 * @param title      标题<中文，英文>列表
	 * @param list       实体数据列表
	 * @author shujukuss
	 * @date 2018年12月28日 下午4:10:14
	 */
	public static byte[] exportXls(String sheetTitle, List<Map<String, String>> title, List<Object> list) {

		HSSFWorkbook wb = new HSSFWorkbook();// 创建03 excel表

		HSSFSheet sheet = wb.createSheet(sheetTitle);
		sheet.setDefaultColumnWidth(20);// 设置默认行宽

		// 表头样式（加粗，水平居中，垂直居中）
		HSSFCellStyle cellStyle = wb.createCellStyle();
		cellStyle.setAlignment(HorizontalAlignment.CENTER);// 水平居中
		cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
		// 设置边框样式
		cellStyle.setBorderBottom(BorderStyle.THIN); // 下边框
		cellStyle.setBorderLeft(BorderStyle.THIN);// 左边框
		cellStyle.setBorderTop(BorderStyle.THIN);// 上边框
		cellStyle.setBorderRight(BorderStyle.THIN);// 右边框

		HSSFFont fontStyle = wb.createFont();
		fontStyle.setBold(true);// 加粗

		cellStyle.setFont(fontStyle);

		// 标题样式（加粗，垂直居中）
		HSSFCellStyle cellStyle2 = wb.createCellStyle();
		cellStyle2.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
		cellStyle2.setFont(fontStyle);

		// 设置边框样式
		cellStyle2.setBorderBottom(BorderStyle.THIN); // 下边框
		cellStyle2.setBorderLeft(BorderStyle.THIN);// 左边框
		cellStyle2.setBorderTop(BorderStyle.THIN);// 上边框
		cellStyle2.setBorderRight(BorderStyle.THIN);// 右边框

		// 字段样式（垂直居中）
		HSSFCellStyle cellStyle3 = wb.createCellStyle();
		cellStyle3.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中

		// 设置边框样式
		cellStyle3.setBorderBottom(BorderStyle.THIN); // 下边框
		cellStyle3.setBorderLeft(BorderStyle.THIN);// 左边框
		cellStyle3.setBorderTop(BorderStyle.THIN);// 上边框
		cellStyle3.setBorderRight(BorderStyle.THIN);// 右边框

		// 创建表头
		HSSFRow row = sheet.createRow(0);
		row.setHeightInPoints(20);// 行高

		HSSFCell cell = row.createCell(0);
		cell.setCellValue(sheetTitle);
		cell.setCellStyle(cellStyle);

		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, (title.size() == 1 ? 2 : title.size() - 1)));

		// 创建标题
		HSSFRow rowTitle = sheet.createRow(1);
		rowTitle.setHeightInPoints(20);

		HSSFCell hc;
		for (int i = 0; i < title.size(); i++) {
			hc = rowTitle.createCell(i);
			hc.setCellValue(title.get(i).keySet().toString());
			hc.setCellStyle(cellStyle2);
		}

		byte result[] = null;

		ByteArrayOutputStream out = null;

		try {
			// 创建表格数据
			Field[] fields;

			for (int i = 0; i < list.size(); i++) {

				Object obj = list.get(i);
				fields = obj.getClass().getDeclaredFields();

				HSSFRow rowBody = sheet.createRow(i + 2);
				rowBody.setHeightInPoints(20);

				for (int j = 0; j < title.size(); j++) {
					for (Field f : fields) {
						f.setAccessible(true);
						if (f.getName().equals(title.get(j).values().toArray()[0])) {
							Object va = f.get(obj);
							if (null == va) {
								va = "";
							}
							hc = rowBody.createCell(j);
							hc.setCellValue(va.toString());
							hc.setCellStyle(cellStyle3);
						}
					}
				}
			}

			out = new ByteArrayOutputStream();
			wb.write(out);
			result = out.toByteArray();
		} catch (Exception ex) {
			Logger.getLogger(ExcelUtils.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			try {
				if (null != out) {
					out.close();
				}
			} catch (IOException ex) {
				Logger.getLogger(ExcelUtils.class.getName()).log(Level.SEVERE, null, ex);
			} finally {
				try {
					wb.close();
				} catch (IOException ex) {
					Logger.getLogger(ExcelUtils.class.getName()).log(Level.SEVERE, null, ex);
				}
			}
		}
		return result;
	}

	/**
	 * @Description: 导出07xlsx
	 * @param sheetTitle sheet标题
	 * @param title      标题[0]中文，[1]英文列表
	 * @param list       实体数据列表
	 * @author shujukuss
	 * @date 2019年3月26日 下午5:10:42
	 */
	public static byte[] exportXlsx(String sheetTitle, List<String[]> title, List<?> list) {

		XSSFWorkbook wb = new XSSFWorkbook();// 创建07 excel表

		XSSFSheet sheet = wb.createSheet(sheetTitle);
		sheet.setDefaultColumnWidth(20);// 设置默认行宽

		// 表头样式（加粗，水平居中，垂直居中）
		XSSFCellStyle cellStyle = wb.createCellStyle();
		cellStyle.setAlignment(HorizontalAlignment.CENTER);// 水平居中
		cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
		// 设置边框样式
		cellStyle.setBorderBottom(BorderStyle.THIN); // 下边框
		cellStyle.setBorderLeft(BorderStyle.THIN);// 左边框
		cellStyle.setBorderTop(BorderStyle.THIN);// 上边框
		cellStyle.setBorderRight(BorderStyle.THIN);// 右边框

		XSSFFont fontStyle = wb.createFont();
		fontStyle.setBold(true);// 加粗

		cellStyle.setFont(fontStyle);

		// 标题样式（加粗，垂直居中）
		XSSFCellStyle cellStyle2 = wb.createCellStyle();
		cellStyle2.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
		cellStyle2.setFont(fontStyle);

		// 设置边框样式
		cellStyle2.setBorderBottom(BorderStyle.THIN); // 下边框
		cellStyle2.setBorderLeft(BorderStyle.THIN);// 左边框
		cellStyle2.setBorderTop(BorderStyle.THIN);// 上边框
		cellStyle2.setBorderRight(BorderStyle.THIN);// 右边框

		// 字段样式（垂直居中）
		XSSFCellStyle cellStyle3 = wb.createCellStyle();
		cellStyle3.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中

		// 设置边框样式
		cellStyle3.setBorderBottom(BorderStyle.THIN); // 下边框
		cellStyle3.setBorderLeft(BorderStyle.THIN);// 左边框
		cellStyle3.setBorderTop(BorderStyle.THIN);// 上边框
		cellStyle3.setBorderRight(BorderStyle.THIN);// 右边框

		// 创建表头
		XSSFRow row = sheet.createRow(0);
		row.setHeightInPoints(20);// 行高

		XSSFCell cell = row.createCell(0);
		cell.setCellValue(sheetTitle);
		cell.setCellStyle(cellStyle);

		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, (title.size() == 1 ? 2 : title.size() - 1)));

		// 创建标题
		XSSFRow rowTitle = sheet.createRow(1);
		rowTitle.setHeightInPoints(20);

		XSSFCell hc;
		// 打印中文标题zhTitle，同时获取英文属性列表
		List<String> enTitleList = new ArrayList<>(1 << 5);
		for (int i = 0; i < title.size(); i++) {
			hc = rowTitle.createCell(i);
			String zhTitle = title.get(i)[0];
			hc.setCellValue(zhTitle);
			hc.setCellStyle(cellStyle2);
			enTitleList.add(title.get(i)[1]);
		}

		byte result[] = null;

		ByteArrayOutputStream out = new ByteArrayOutputStream();
		try {
			// 创建表格数据
		    System.out.println(LocalTime.now() + "----------start");
			if (list != null && !list.isEmpty() && list.get(0) != null) {
			    for (int i = 0; i < list.size(); i++) {
	                Object obj = list.get(i);
	                Field[] fields = obj.getClass().getDeclaredFields();
	                Map<String, Object> map = new HashMap<>(1 << 5);
	                for (Field f : fields) {
	                    f.setAccessible(true);
	                    map.put(f.getName(), f.get(obj));
	                }
	                XSSFRow rowBody = sheet.createRow(i + 2);
	                rowBody.setHeightInPoints(20);
	                for (int j = 0; j < enTitleList.size(); j++) {
	                    Object va = map.get(enTitleList.get(j));
	                    if (null == va) {
	                        va = "";
	                    }
	                    if("sex".equals(enTitleList.get(j))) {
	                        va = (Long)va == 0L ? "女" : "男";
	                    }
	                    if(TRANSFORMDATE_LIST.contains(enTitleList.get(j))) {
	                        va = DateUtils.date2StrYearMonth((Date)va);
	                    }
	                    hc = rowBody.createCell(j);
	                    hc.setCellValue(va.toString());
	                    hc.setCellStyle(cellStyle3);
	                }
	            }
			}
			System.out.println(LocalTime.now() + "----------end");
			wb.write(out);
			result = out.toByteArray();
		} catch (Exception ex) {
			Logger.getLogger(ExcelUtils.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			try {
				if (null != out) {
					out.close();
				}
			} catch (IOException ex) {
				Logger.getLogger(ExcelUtils.class.getName()).log(Level.SEVERE, null, ex);
			} finally {
				try {
					wb.close();
				} catch (IOException ex) {
					Logger.getLogger(ExcelUtils.class.getName()).log(Level.SEVERE, null, ex);
				}
			}
		}
		return result;
	}

	/*
	 * 下载Excel文件名定义
	 */
	public static String generateExcelName(String rawName) {
		if (rawName == null) {
			return "文件.xlsx";
		}

		// 文件名中包含当天日期yyyyMMdd
		String regexDate = "\\{currentDate\\}";
		SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
		String now = sdf.format(new Date());
		rawName = rawName.replaceAll(regexDate, now);
		return rawName;
	}

	public static Map<String, List<?>> getExcel2007Data(XSSFWorkbook wb) {
		Map<String,List<?>> result = new HashMap<String,List<?>>();
		int num = wb.getNumberOfSheets();
		List<Map<String,Integer>> titleList = new ArrayList<Map<String,Integer>>();
		for (int i = 0; i < num; i++) {
			XSSFSheet sheet = wb.getSheetAt(i);
			Map<String,Integer> tMap = new HashMap<String,Integer>();
			XSSFRow row = sheet.getRow(1);
			if (row != null) {
				int cellNum = row.getLastCellNum();
				for (int j = 0; j < cellNum; j++) {
					XSSFCell cell = row.getCell(j);
					if (cell != null) {
						tMap.put(cell.getStringCellValue().toLowerCase(), Integer.valueOf(j));
					}
				}
			}
			titleList.add(tMap);
		}

		List<List<Map<Integer,String>>> dataList = new ArrayList<List<Map<Integer,String>>>();
		for (int i = 0; i < num; i++) {
			XSSFSheet sheet = wb.getSheetAt(i);
			int rowNum = sheet.getPhysicalNumberOfRows();
			List<Map<Integer,String>> items = new ArrayList<Map<Integer,String>>();
			for (int j = 2; j < rowNum; j++) {
				XSSFRow row = sheet.getRow(j);
				if (row != null) {
					Map<Integer,String> colMap = new HashMap<Integer,String>();
					int cellNum = row.getLastCellNum();
					for (int k = 0; k < cellNum; k++) {
						String cellValue = "";
						XSSFCell cell = row.getCell(k);
						if (cell != null) {
							cellValue = getCellFormatValue(cell);
							colMap.put(Integer.valueOf(k), cellValue);
						}
					}
					items.add(colMap);
				}
			}
			dataList.add(items);
		}

		result.put("titleList", titleList);
		result.put("dataList", dataList);
		return result;
	}

	public static Map<String, List<?>> getExcel2003Data(HSSFWorkbook wb) {
		Map<String, List<?>> result = new HashMap<String, List<?>>();
		int num = wb.getNumberOfSheets();
		List<Map<String,Integer>> titleList = new ArrayList<Map<String,Integer>>();
		for (int i = 0; i < num; i++) {
			HSSFSheet sheet = wb.getSheetAt(i);
			Map<String,Integer> tMap = new HashMap<String,Integer>();
			HSSFRow row = sheet.getRow(1);
			if (row != null) {
				int cellNum = row.getLastCellNum();
				for (int j = 0; j < cellNum; j++) {
					HSSFCell cell = row.getCell(j);
					if (cell != null) {
						tMap.put(cell.getStringCellValue().toLowerCase(), Integer.valueOf(j));
					}
				}
			}
			titleList.add(tMap);
		}

		List<List<Map<Integer,String>>> dataList = new ArrayList<List<Map<Integer,String>>>();
		for (int i = 0; i < num; i++) {
			HSSFSheet sheet = wb.getSheetAt(i);
			int rowNum = sheet.getPhysicalNumberOfRows();
			List<Map<Integer,String>> items = new ArrayList<Map<Integer,String>>();
			for (int j = 2; j < rowNum; j++) {
				HSSFRow row = sheet.getRow(j);
				if (row != null) {
					Map<Integer,String> colMap = new HashMap<Integer,String>();
					int cellNum = row.getLastCellNum();
					for (int k = 0; k < cellNum; k++) {
						String cellValue = "";
						HSSFCell cell = row.getCell(k);
						if (cell != null) {
							cellValue = getCellFormatValue(cell);
							colMap.put(Integer.valueOf(k), cellValue);
						}
					}
					items.add(colMap);
				}
			}
			dataList.add(items);
		}
		result.put("titleList", titleList);
		result.put("dataList", dataList);
		return result;
	}

	public static String getCellFormatValue(Cell cell) {
		String cellValue = null;
		switch (cell.getCellType()) {
		case NUMERIC: {
			cellValue = String.valueOf(cell.getNumericCellValue());
			break;
		}
		case STRING: {
			cellValue = cell.getStringCellValue();
			break;
		}
		case FORMULA:
			if (HSSFDateUtil.isCellDateFormatted(cell))
				cellValue = TypeUtils.date2String(cell.getDateCellValue());
			else {
				cellValue = TypeUtils.formatNumber(cell.getNumericCellValue());
			}
			break;
		}
		return cellValue;
	}

}